﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MachineVision.Service.Dto;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace MachineVision.Service
{
    public class ResultMsg
    {
        public string ErrMsg { get; set; }
        public string PathStr { get; set; }
        public bool Success { get; set; }
    }
    public class ExcelExtend: IExcelExtend
    {
        public static ResultMsg ToExcelFile(MissionNum mission , List<MissionHistory> list)
        {
            ResultMsg msg = new ResultMsg();
            if (!Directory.Exists(Environment.CurrentDirectory + "\\Datas\\Excels"))
            {
                Directory.CreateDirectory(Environment.CurrentDirectory + "\\Datas\\Excels");
            }
            var pathstr = "\\Datas\\Excels" + $"\\{DateTime.Now.ToString("yyMMdd-hhmmssff")}.xlsx";


            var fileName = $"{Environment.CurrentDirectory}\\Datas\\巡检汇总报表.xlsx";
            IWorkbook workbook = null;
            ISheet sheet = null;
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            workbook = new XSSFWorkbook(fs);
            sheet = workbook.GetSheetAt(0);//获取第一个工作表
            try
            {
                if (sheet != null)
                {
                    int LocalRow = 1;
                    //IRow SetThisRow = sheet.GetRow(LocalRow);
                    sheet.GetRow(1).Cells[2].SetCellValue(mission.StartTime.ToString("yyyy-MM-dd"));
                    sheet.GetRow(1).Cells[5].SetCellValue(mission.StartTime.ToString("hh:mm"));
                    sheet.GetRow(1).Cells[8].SetCellValue(list.Max(s=>s.EndTime).ToString("hh:mm"));
                    LocalRow++;//第二行
                    sheet.GetRow(2).Cells[2].SetCellValue(list.Count);
                    sheet.GetRow(2).Cells[5].SetCellValue(list.Count(s=>s.ResultValue!=0));
                    LocalRow++;
                    //"LM29","LM37","LM14","LM50"
                    sheet.GetRow(3).Cells[2].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM29")?.Temperature.ToString() + "℃");
                    sheet.GetRow(3).Cells[4].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM37")?.Temperature.ToString() + "℃");
                    sheet.GetRow(3).Cells[6].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM14")?.Temperature.ToString() + "℃");
                    sheet.GetRow(3).Cells[8].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM50")?.Temperature.ToString() + "℃");
                    LocalRow++;                      
                    sheet.GetRow(4).Cells[2].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM29")?.Humidity.ToString()+"%");
                    sheet.GetRow(4).Cells[4].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM37")?.Humidity.ToString() + "%");
                    sheet.GetRow(4).Cells[6].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM14")?.Humidity.ToString() + "%");
                    sheet.GetRow(4).Cells[8].SetCellValue(list.FirstOrDefault(s=>s.Name == "LM50")?.Humidity.ToString() + "%");

                    LocalRow =7;//跳过行
                    #region 专业名称
                    if (list.Any())
                    {
                        //复制行完成后插入数据
                        list.ForEach(s =>
                        {
                            var ss = sheet.GetRow(LocalRow).Cells[0].ToString();
                            sheet.GetRow(LocalRow).Cells[1].SetCellValue(s.Name); 
                            sheet.GetRow(LocalRow).Cells[2].SetCellValue(s.Type.ToString()); 
                            sheet.GetRow(LocalRow).Cells[3].SetCellValue(s.StartTime.ToString("hh:mm:ss"));
                            sheet.GetRow(LocalRow).Cells[4].SetCellValue(s.EndTime.ToString("hh:mm:ss"));
                            sheet.GetRow(LocalRow).Cells[5].SetCellValue(s.WorkPlace.ToString());
                            sheet.GetRow(LocalRow).Cells[6].SetCellValue(s.DetectionResult);// (s.ResultValue==0?"正常":"异常"); 
                            sheet.GetRow(LocalRow).Cells[7].SetCellValue(s.Terminal);
                            LocalRow++;
                        });
                    }
                    #endregion



                    FileStream streamFile = new FileStream(Environment.CurrentDirectory + pathstr, FileMode.Create);
                    System.IO.MemoryStream streamMemory = new System.IO.MemoryStream();
                    workbook.Write(streamMemory);
                    byte[] data = streamMemory.ToArray();

                    // 开始写入
                    streamFile.Write(data, 0, data.Length);

                    // 清空缓冲区、关闭流
                    streamFile.Flush();
                    streamFile.Close();
                    workbook = null;
                    streamMemory.Close();
                    streamMemory.Dispose();
                    msg.Success = true;
                    msg.PathStr = pathstr;
                }
                else
                {
                    msg.Success = false;
                    msg.ErrMsg = "未找到表格";
                }
            }
            catch (Exception ex)
            {
                msg.Success = false;
                msg.ErrMsg = ex.Message;
            }
            return msg;
        }

        /// <summary>
        /// 复制行格式并插入指定行数
        /// </summary>
        /// <param name="sheet">当前sheet</param>
        /// <param name="startRowIndex">起始行位置</param>
        /// <param name="sourceRowIndex">模板行位置</param>
        /// <param name="insertCount">插入行数</param>
        public static void CopyRow(ISheet sheet, int startRowIndex, int sourceRowIndex, int insertCount)
        {
            IRow sourceRow = sheet.GetRow(sourceRowIndex);
            int sourceCellCount = sourceRow.Cells.Count;

            //1. 批量移动行,清空插入区域
            sheet.ShiftRows(startRowIndex, //开始行
                            sheet.LastRowNum, //结束行
                            insertCount, //插入行总数
                            true,        //是否复制行高
                            false        //是否重置行高
                            );

            int startMergeCell = -1; //记录每行的合并单元格起始位置
            for (int i = startRowIndex; i < startRowIndex + insertCount; i++)
            {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;

                targetRow = sheet.CreateRow(i);
                targetRow.Height = sourceRow.Height;//复制行高

                for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
                {
                    sourceCell = sourceRow.GetCell(m);
                    if (sourceCell == null)
                        continue;
                    targetCell = targetRow.CreateCell(m);
                    targetCell.CellStyle = sourceCell.CellStyle;//赋值单元格格式
                    targetCell.SetCellType(sourceCell.CellType);
                    if ((sourceCell.IsMergedCell && !string.IsNullOrWhiteSpace(sourceCell.StringCellValue)))
                    {
                        if (startMergeCell < m - 1)//如果第一个是合并单元格并且是竖行的合并单元格就就取消合并
                        {
                            if (startMergeCell >= 0)
                            {
                                sheet.AddMergedRegion(new CellRangeAddress(i, i, startMergeCell, m - 1));
                            }
                        }
                        startMergeCell = m;
                    }
                    else if ((sourceCell.IsMergedCell && string.IsNullOrWhiteSpace(sourceCell.StringCellValue)) || m == sourceRow.LastCellNum - 1)
                    {
                        if (m == sourceRow.LastCellNum - 1 && startMergeCell >= 0)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(i, i, startMergeCell, m));
                        }
                        continue;
                    }
                    else
                    {
                        if (startMergeCell < m - 1)//如果第一个是合并单元格并且是竖行的合并单元格就就取消合并
                        {
                            if (startMergeCell >= 0)
                            {
                                sheet.AddMergedRegion(new CellRangeAddress(i, i, startMergeCell, m - 1));
                                startMergeCell = -1;
                            }
                        }
                        else
                        {
                            startMergeCell = -1;
                        }

                    }
                }
            }
        }


        public static void TestExcel()
        {
            var fileName = @"C:\Users\Administrator\Desktop\TOOL\巡检汇总报表.xlsx";
            //判断文件是否存在，不存在会报错，所以这里做了过滤
            if (!File.Exists(fileName))
            {
                return;
            }
            //根据指定路径读取文件
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            //根据文件流创建excel数据结构
            IWorkbook workbook = WorkbookFactory.Create(fs);

            //尝试获取第一个sheet
            var sheet = workbook.GetSheetAt(0);
            //判断是否获取到 sheet
            if (sheet != null)
            {
                //获取第一行
                var row = sheet.GetRow(0);
                for (int i = 0; i < row.Count(); i++)
                {
                    //输出
                    Console.WriteLine($"第一行数据：第 {i} 个数据值：{row.GetCell(i).ToString()}");
                }
            }
        }

    }
}
